キーワード RIGHT JOIN は、右側のテーブル (table2) のすべてのレコードと、左側のテーブル (table1) の対応するレコードを返します。
一致しない場合、結果は左から 0 レコードになります。
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name
このチュートリアルでは、有名なサンプル データベース「Northwind」を使用します。
以下はテーブル「Orders」(「注文」) のサンプルです。
| ProductID | OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|---|
| 1 | 10248 | 90 | 5 | 1996-07-04 | 3 |
| 2 | 10249 | 81 | 6 | 1996-07-05 | 1 |
| 3 | 10250 | 34 | 4 | 1996-07-08 | 2 |
| 4 | 10251 | 84 | 3 | 1996-07-08 | 1 |
| 5 | 10252 | 76 | 4 | 1996-07-09 | 2 |
そして、テーブル "Employees" ("従業員") からの選択:
| EmployeeID | LastName | FirstName | BirthDate | Photo | Notes |
|---|---|---|---|---|---|
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic | Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'. |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic | Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association. |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic | Janet has a BS degree in chemistry from Boston College). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate and was promoted to sales representative. |
| 4 | Peacock | Margaret | 9/19/1958 | EmpID4.pic | Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle. |
| 5 | Buchanan | Steven | 3/4/1955 | EmpID5.pic | Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager. Mr. Buchanan has completed the courses 'Successful Telemarketing' and 'International Sales Management'. He is fluent in French. |
次の SQL ステートメントは、すべての従業員とその従業員が行った注文を返します。
Run SQLSELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID